/*
 * This file is released under the terms of the Artistic License.  Please see
 * the file LICENSE, included in this package, for details.
 *
 * Copyright (C) 2006 Rilson Nascimento
 *
 * Trade Update transaction
 * ------------------------
 * The Trade-Update transaction is designed to emulate information retrieval and
 * possibly modification by either a customer or a broker to satisfy questions
 * regarding a particular account, a group of trade transaction identifiers or
 * a particular security.
 *
 * Based on TPC-E Standard Specification Draft Revision 0.32.2e Clause 3.3.4.
 */

/*
 * Frame 1
 * responsible for retrieving information about the specified array of trade
 * IDs and modifying some data from the TRADE table.
 */

CREATE OR REPLACE FUNCTION TradeUpdateFrame1 (
        IN max_trades INTEGER,
        IN max_updates INTEGER,
        IN trade_id BIGINT[],
        OUT bid_price NUMERIC(8,2)[],
        OUT cash_transaction_amount NUMERIC(10,2)[],
        OUT cash_transaction_dts TIMESTAMP[],
        OUT cash_transaction_name VARCHAR(100)[],
        OUT exec_name VARCHAR(64)[],
        OUT is_cash BOOLEAN[],
        OUT is_market BOOLEAN[],
        OUT num_found INTEGER,
        OUT num_updated INTEGER,
        OUT settlement_amount NUMERIC(10,2)[],
        OUT settlement_cash_due_date TIMESTAMP[],
        OUT settlement_cash_type VARCHAR(40)[],
        OUT trade_history_dts VARCHAR,
        OUT trade_history_status_id VARCHAR,
        OUT trade_price NUMERIC(8,2)[]
) RETURNS SETOF record AS $$
DECLARE
	-- variables
    _trade_history_dts TIMESTAMP[];
    _trade_history_status_id VARCHAR(4)[];
    _char_th_dts                 VARCHAR;
    _char_thst_id                VARCHAR;
    _trade_id                    BIGINT;
	ex_name	VARCHAR(64);
	i		INTEGER;
	irow_count	INTEGER;
	rs		RECORD;
BEGIN
	num_found = 0;
	num_updated = 0;
    trade_history_dts = '{';
    trade_history_status_id = '{';

	i = 1;
	WHILE i <= max_trades LOOP
		-- Get trade information
        _trade_id = trade_id[i];

		IF num_updated < max_updates THEN
			-- Modify the TRADE row for this trade
			SELECT
                    T_EXEC_NAME
			INTO	
                    ex_name
			FROM 	
                    TRADE
			WHERE	
                    T_ID = _trade_id;

            num_found = num_found + 1;

			IF ex_name like '% X %' THEN
				SELECT replace(ex_name, ' X ', ' ') INTO ex_name;
			ELSE
				SELECT replace(ex_name, ' ', ' X ')	INTO ex_name;
			END IF;

			UPDATE
                TRADE
			SET	
                T_EXEC_NAME = ex_name
			WHERE
                T_ID = _trade_id;

			GET DIAGNOSTICS irow_count = ROW_COUNT;
			num_updated = num_updated + irow_count;
		END IF;

		-- will only return one row for each trade

		FOR rs IN
           SELECT
               T_BID_PRICE,
               T_EXEC_NAME,
		       T_IS_CASH,
			   TT_IS_MRKT,
			   T_TRADE_PRICE
		   FROM
               TRADE,
			   TRADE_TYPE
		   WHERE
               T_ID = _trade_id AND
			   T_TT_ID = TT_ID
		LOOP
            bid_price[i] = rs.T_BID_PRICE;
			exec_name[i] = rs.T_EXEC_NAME;
			is_cash[i] = rs.T_IS_CASH;
			is_market[i] = rs.TT_IS_MRKT;
			trade_price[i] = rs.T_TRADE_PRICE;
        END LOOP;
		-- Get settlement information
		-- Will only return one row for each trade

		FOR rs IN
            SELECT	
                SE_AMT,
			    SE_CASH_DUE_DATE,
			    SE_CASH_TYPE
		    FROM
                SETTLEMENT
		    WHERE
                SE_T_ID = _trade_id
	    LOOP
            settlement_amount[i] = rs.SE_AMT;
			settlement_cash_due_date[i] = rs.SE_CASH_DUE_DATE;
			settlement_cash_type[i] = rs.SE_CASH_TYPE;
        END LOOP;
		-- get cash information if this is a cash transaction
		-- Will only return one row for each trade that was a cash transaction

		IF is_cash[i] THEN
			FOR rs IN
                SELECT
                    CT_AMT,
				    CT_DTS,
				    CT_NAME
			    FROM
                    CASH_TRANSACTION
			    WHERE
                    CT_T_ID = _trade_id
            LOOP
                cash_transaction_amount[i] = rs.CT_AMT;
				cash_transaction_dts[i] = rs.CT_DTS;
				cash_transaction_name[i] = rs.CT_NAME;
            END LOOP;
        ELSE
            cash_transaction_amount[i] = NULL;
            cash_transaction_dts[i] = NULL;
            cash_transaction_name[i] = NULL;
		END IF;

		-- read trade_history for the trades
		-- Will return 2 to 3 rows per trade

        _trade_history_dts = '{}';
        _trade_history_status_id = '{}';
		FOR rs IN
            SELECT
                   TH_DTS,
                   TH_ST_ID 
			FROM
                   TRADE_HISTORY
			WHERE
                   TH_T_ID = _trade_id
            ORDER BY TH_DTS 
        LOOP
			_trade_history_dts = _trade_history_dts || rs.TH_DTS;
			_trade_history_status_id = _trade_history_status_id || rs.TH_ST_ID;
		END LOOP;
        _char_th_dts = _trade_history_dts;
        _char_thst_id = _trade_history_status_id;

        trade_history_dts = trade_history_dts || _char_th_dts || ',';
        trade_history_status_id = trade_history_status_id || _char_thst_id || ',';    

		i = i + 1;
	END LOOP;

    trade_history_dts = trim(trailing from trade_history_dts, ',');
    trade_history_dts = trade_history_dts || '}';

    trade_history_status_id = trim(trailing from trade_history_status_id, ',');
    trade_history_status_id = trade_history_status_id || '}';

    RETURN NEXT;
END;
$$ LANGUAGE 'plpgsql';


/*
 * Frame 2
 * 
 * 
 */

CREATE OR REPLACE FUNCTION TradeUpdateFrame2(
        IN acct_id BIGINT,
        IN end_trade_dts TIMESTAMP,
        IN max_trades INTEGER,
        IN max_updates INTEGER,
        IN start_trade_dts TIMESTAMP,
        OUT bid_price NUMERIC(8,2)[],
        OUT cash_transaction_amount NUMERIC(10,2)[],
        OUT cash_transaction_dts TIMESTAMP[],
        OUT cash_transaction_name VARCHAR(100)[],
        OUT exec_name VARCHAR(64)[],
        OUT is_cash BOOLEAN[],
        OUT num_found INTEGER,
        OUT num_updated INTEGER,
        OUT settlement_amount NUMERIC(10,2)[],
        OUT settlement_cash_due_date TIMESTAMP[],
        OUT settlement_cash_type VARCHAR(40)[],
        OUT trade_history_dts       VARCHAR,
        OUT trade_history_status_id VARCHAR,
        OUT trade_list BIGINT[],
        OUT trade_price NUMERIC(8,2)[]
) RETURNS SETOF record AS $$
DECLARE
	-- variables
    _trade_history_dts TIMESTAMP[];
    _trade_history_status_id VARCHAR(4)[];
    _char_th_dts                 VARCHAR;
    _char_thst_id                VARCHAR;
    _trade_id                    BIGINT;
	i		INTEGER;
	rs		RECORD;
	cash_type	VARCHAR(40);
	irow_count	INTEGER;
BEGIN
	-- Get trade information
	-- Should return between 0 and max_trades rows
    num_found = 0;
    num_updated = 0;
    trade_history_dts = '{';
    trade_history_status_id = '{';

	i = 0; 
	FOR rs IN 
        SELECT 
            T_BID_PRICE,
			T_EXEC_NAME,
			T_IS_CASH,
			T_ID,
			T_TRADE_PRICE
		FROM
            TRADE
		WHERE
            T_CA_ID = acct_id AND
            T_DTS >= start_trade_dts AND
            T_DTS <= end_trade_dts
		ORDER BY
            T_DTS asc
		LIMIT max_trades
    LOOP
        i = i + 1;
        bid_price[i]   = rs.T_BID_PRICE;
        exec_name[i]   = rs.T_EXEC_NAME;
        is_cash[i]     = rs.T_IS_CASH;
        trade_list[i]  = rs.T_ID;
        trade_price[i] = rs.T_TRADE_PRICE;
    END LOOP;

    num_found = i;

    i = 1;
    WHILE i <= num_found LOOP

        _trade_id = trade_list[i];

        IF num_updated < max_updates THEN
			-- Select the SETTLEMENT row for this trade
			SELECT
                    SE_CASH_TYPE
            INTO
                    cash_type
            FROM
                    SETTLEMENT
			WHERE
                    SE_T_ID = _trade_id;

			IF is_cash[i] THEN
				IF cash_type = 'Cash Account' THEN
					cash_type = 'Cash';
				ELSE
					cash_type = 'Cash Account';
				END IF;
			ELSE
				IF cash_type = 'Margin Account' THEN
					cash_type = 'Margin';
				ELSE
					cash_type = 'Margin Account';
				END IF;				
			END IF;

			UPDATE
                SETTLEMENT
			SET
                SE_CASH_TYPE = cash_type
			WHERE
                SE_T_ID = _trade_id;

			GET DIAGNOSTICS irow_count = ROW_COUNT;
			num_updated = num_updated + irow_count;
		END IF;

		-- Get settlement information
		-- Should return only one row for each trade

		FOR rs IN
            SELECT	
                SE_AMT,
			    SE_CASH_DUE_DATE,
			    SE_CASH_TYPE
		    FROM
                SETTLEMENT
		    WHERE
                SE_T_ID = _trade_id
        LOOP
            settlement_amount[i] = rs.SE_AMT;
			settlement_cash_due_date[i] = rs.SE_CASH_DUE_DATE;
			settlement_cash_type[i] = rs.SE_CASH_TYPE;
        END LOOP;

		-- get cash information if this is a cash transaction
		-- Should return only one row for each trade that was a cash transaction

		IF is_cash[i] THEN
			FOR rs IN
                SELECT
                    CT_AMT,
 				    CT_DTS,
				    CT_NAME
			    FROM
                    CASH_TRANSACTION
			    WHERE
                    CT_T_ID = _trade_id
            LOOP
                cash_transaction_amount[i] = rs.CT_AMT;
				cash_transaction_dts[i] = rs.CT_DTS;
				cash_transaction_name[i] = rs.CT_NAME;
            END LOOP;
        ELSE
            cash_transaction_amount[i] = NULL;
            cash_transaction_dts[i] = NULL;
            cash_transaction_name[i] = NULL;
		END IF;

		-- read trade_history for the trades
		-- Should return 2 to 3 rows per trade
        _trade_history_dts = '{}';
        _trade_history_status_id = '{}';
		FOR rs IN
            SELECT 
                TH_DTS,
                TH_ST_ID 
			FROM
                TRADE_HISTORY
			WHERE
                TH_T_ID = _trade_id
            ORDER BY TH_DTS
        LOOP
			_trade_history_dts = _trade_history_dts || rs.TH_DTS;
			_trade_history_status_id = _trade_history_status_id || rs.TH_ST_ID;
		END LOOP;
        _char_th_dts = _trade_history_dts;
        _char_thst_id = _trade_history_status_id;

        trade_history_dts = trade_history_dts || _char_th_dts || ',';
        trade_history_status_id = trade_history_status_id || _char_thst_id || ',';

        i = i + 1;
    END LOOP;

    trade_history_dts = trim(trailing from trade_history_dts, ',');
    trade_history_dts = trade_history_dts || '}';

    trade_history_status_id = trim(trailing from trade_history_status_id, ',');
    trade_history_status_id = trade_history_status_id || '}';

    RETURN NEXT;
END;
$$ LANGUAGE 'plpgsql';


/*
 * Frame 3
 * returns up to N (max_trades) trades for a given security on or after a
 * specified point in time
 * and modifies some data from the CASH_TRANSACTION table.
 */

CREATE OR REPLACE FUNCTION TradeUpdateFrame3(
        IN end_trade_dts TIMESTAMP,
        IN max_acct_id BIGINT,
        IN max_trades INTEGER,
        IN max_updates INTEGER,
        IN start_trade_dts TIMESTAMP,
        IN symbol VARCHAR(15),
        OUT acct_id BIGINT[],
        OUT cash_transaction_amount NUMERIC(10,2)[],
        OUT cash_transaction_dts TIMESTAMP[],
        OUT cash_transaction_name VARCHAR(100)[],
        OUT exec_name VARCHAR(64)[],
        OUT is_cash BOOLEAN[],
        OUT num_found INTEGER,
        OUT num_updated INTEGER,
        OUT price NUMERIC(8,2)[],
        OUT quantity INTEGER[],
        OUT s_name VARCHAR(70)[],
        OUT settlement_amount NUMERIC(10,2)[],
        OUT settlement_cash_due_date TIMESTAMP[],
        OUT settlement_cash_type VARCHAR(40)[],
        OUT trade_dts TIMESTAMP[],
        OUT trade_history_dts VARCHAR,
        OUT trade_history_status_id VARCHAR,
        OUT trade_list BIGINT[],
        OUT type_name VARCHAR(12)[],
        OUT trade_type VARCHAR(3)[]
) RETURNS SETOF record AS $$
DECLARE
    _trade_history_dts TIMESTAMP[];
    _trade_history_status_id VARCHAR(4)[];
    _char_th_dts                 VARCHAR;
    _char_thst_id                VARCHAR;
    _trade_id                    BIGINT;
	-- Local Frame variables
	i		    INTEGER;
	rs		    RECORD;
	cash_name	VARCHAR(100);
	irow_count	INTEGER;
BEGIN
	-- Should return between 0 and max_trades rows.
    num_found = 0; 
	num_updated = 0;
    trade_history_dts = '{';
    trade_history_status_id = '{';

    i = 0;
	FOR rs IN
        SELECT
            TR.T_CA_ID,
			TR.T_EXEC_NAME,
			TR.T_IS_CASH,
			TR.T_TRADE_PRICE,
			TR.T_QTY,
            SEC.S_NAME,
			TR.T_DTS,
            TR.T_ID,
			TR.T_TT_ID,
			TT.TT_NAME
		FROM
            TRADE TR,
			TRADE_TYPE TT,
			SECURITY SEC
		WHERE
            TR.T_S_SYMB = symbol AND
			TR.T_DTS >= start_trade_dts AND
            TR.T_DTS <= end_trade_dts AND
			TT.TT_ID = TR.T_TT_ID AND
			SEC.S_SYMB = TR.T_S_SYMB
		ORDER BY 
            T_DTS asc
		LIMIT max_trades
    LOOP
        i = i + 1;
        acct_id[i]    = rs.T_CA_ID;
        exec_name[i]  = rs.T_EXEC_NAME;
        is_cash[i]    = rs.T_IS_CASH;
        price[i]      = rs.T_TRADE_PRICE;
        quantity[i]   = rs.T_QTY;
        s_name[i]     = rs.S_NAME;
        trade_dts[i]  = rs.T_DTS;
        trade_list[i] = rs.T_ID;
        trade_type[i] = rs.T_TT_ID;
        type_name[i]  = rs.TT_NAME;
    END LOOP;

    num_found = i;

    -- Get extra information for each trade in the trade list.
    i = 1;
    WHILE i <= num_found LOOP
        -- Get settlement information
        -- Will return only one row for each trade
        _trade_id = trade_list[i];
		FOR rs IN
            SELECT
                SE_AMT,
			    SE_CASH_DUE_DATE,
			    SE_CASH_TYPE
		    FROM
                SETTLEMENT
		    WHERE
                SE_T_ID = _trade_id
        LOOP
            settlement_amount[i] = rs.SE_AMT;
			settlement_cash_due_date[i] = rs.SE_CASH_DUE_DATE;
			settlement_cash_type[i] = rs.SE_CASH_TYPE;
        END LOOP;

		-- get cash information if this is a cash transaction
		-- Will return only one row for each trade that was a cash transaction

		IF is_cash[i] THEN
			IF num_updated < max_updates THEN
				-- Modify the CASH_TRANSACTION row for this trade
				SELECT
                    CT_NAME
				INTO
                    cash_name
				FROM
                    CASH_TRANSACTION
				WHERE
                    CT_T_ID = _trade_id;

				IF cash_name like '% shares of %' THEN
					cash_name = type_name[i] || ' ' || quantity[i] || ' Shares of ' || s_name[i];
				ELSE
					cash_name = type_name[i] || ' ' || quantity[i] || ' shares of ' || s_name[i];
				END IF;

				UPDATE
                    CASH_TRANSACTION
				SET
                    CT_NAME = cash_name
				WHERE
                    CT_T_ID = _trade_id;

				GET DIAGNOSTICS irow_count = ROW_COUNT;

				num_updated = num_updated + irow_count;
			END IF;

			FOR rs IN
                SELECT
                    CT_AMT,
 				    CT_DTS,
				    CT_NAME
			    FROM
                    CASH_TRANSACTION
			    WHERE
                    CT_T_ID = _trade_id
            LOOP
                cash_transaction_amount[i] = rs.CT_AMT;
				cash_transaction_dts[i] = rs.CT_DTS;
				cash_transaction_name[i] = rs.CT_NAME;
            END LOOP;
        ELSE
            cash_transaction_amount[i] = NULL;
            cash_transaction_dts[i] = NULL;
            cash_transaction_name[i] = NULL;
		END IF;

		-- read trade_history for the trades
		-- Should return 2 to 3 rows per trade
        _trade_history_dts = '{}';
        _trade_history_status_id = '{}';
		FOR rs IN
            SELECT
                TH_DTS,
                TH_ST_ID 
			FROM
                TRADE_HISTORY
			WHERE
                TH_T_ID = _trade_id
            ORDER BY TH_DTS
        LOOP
			_trade_history_dts = _trade_history_dts || rs.TH_DTS;
			_trade_history_status_id = _trade_history_status_id || rs.TH_ST_ID;
		END LOOP;
        _char_th_dts = _trade_history_dts;
        _char_thst_id = _trade_history_status_id;

        trade_history_dts = trade_history_dts || _char_th_dts || ',';
        trade_history_status_id = trade_history_status_id || _char_thst_id || ',';

        i = i + 1;
    END LOOP;

    trade_history_dts = trim(trailing from trade_history_dts, ',');
    trade_history_dts = trade_history_dts || '}';

    trade_history_status_id = trim(trailing from trade_history_status_id, ',');
    trade_history_status_id = trade_history_status_id || '}';

    RETURN NEXT;
END;
$$ LANGUAGE 'plpgsql';
